package com.scalar.chiptrack.finance.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.finance.reports.forms.FinanaceReportForm;
import com.scalar.chiptrack.finance.forms.CostReportsForm;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.ScalarException;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: 20 Jul, 2009
 * Time: 3:24:10 PM
 * To change this template use File | Settings | File Templates.
 */
public class FinanceDataDumpReportDAO
{

    public static ArrayList getYieldCostVarienceReport ( DBTransaction dbTransaction,  FinanaceReportForm financeReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList reportList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_YIELD_COST_VARIENCE_REPORT ( ?, ?, ?, ?, ?, ? ) }" );

            pstmt.setString( 1, financeReportForm.getProcessType() );
            pstmt.setInt( 2, financeReportForm.getProcessStepId() );
            pstmt.setInt( 3, financeReportForm.getBaseProductId() );
            pstmt.setString( 4, financeReportForm.getVendorName() );
            pstmt.setString( 5, financeReportForm.getFromDate() );
            pstmt.setString( 6, financeReportForm.getToDate() );

            rset    = pstmt.executeQuery();

            reportList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Yield Cost Varience report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return reportList;
    }

    public static ArrayList getStdCostReport ( DBTransaction dbTransaction,  CostReportsForm costReportForm ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;

        ArrayList reportList = new ArrayList();

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "{ CALL SP_COST_REPRORT_BY_PRODUCT ( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            
            pstmt.setString( 1, costReportForm.getProcessTypeName() );
            pstmt.setString( 2, costReportForm.getBaseProductId() +"" );
            pstmt.setString( 3, costReportForm.getProcessStepId()+"" );
            pstmt.setString( 4, costReportForm.getVendorId()+"" );
            pstmt.setString( 5, costReportForm.getFromDate() );
            pstmt.setString( 6, costReportForm.getReportType() );
            pstmt.setString( 7, "0" );
            pstmt.setString( 8, "XLREPORT" );

            rset    = pstmt.executeQuery();

            reportList = ExcelUtils.getAllResultSetRows( rset, true );

            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not get Standard Cost report deatils", e );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( SQLException e ){}
                }
            }
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return reportList;
    }

    public static ArrayList<ArrayList> getStdCostVarianceReport(DBTransaction dbTransaction, FinanaceReportForm finanaceReportForm) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        ArrayList reportList = new ArrayList();

        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement("{ CALL SP_COST_VARIANCE_REPORT ( ?, ?, ?, ?, ? ) }");

            pstmt.setString(1, finanaceReportForm.getFromDate());
            pstmt.setInt(2, finanaceReportForm.getFromQtr());
            pstmt.setInt(3, finanaceReportForm.getFromYear());
            pstmt.setInt(4, finanaceReportForm.getToQtr());
            pstmt.setInt(5, finanaceReportForm.getToYear());

            rset = pstmt.executeQuery();

            reportList = ExcelUtils.getAllResultSetRows(rset, true);

            success = true;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Could not get Cost Variance report deatils", e);
        }
        finally {
            if (dbTransaction == null) {
                if (conn != null) {
                    try {
                        if (success) {
                            conn.commit();
                        } else {
                            conn.rollback();
                        }
                    } catch (SQLException e) {
                    }
                }
            }
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return reportList;
    }
}
